﻿CREATE PROCEDURE [util].[SecurityGrantDbExecution]
@login [sysname], @DbName [sysname], @role [sysname]=NULL
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	DECLARE @user sysname, @query nvarchar(4000), @statement nvarchar(4000)
	SET @user = NULL
	SET @query = N'SELECT @user = db.[name] 
		FROM [' + @DbName + N'].dbo.sysusers AS db
		INNER JOIN master.dbo.syslogins AS ms
			ON (ms.[sid] = db.[sid])
			AND(ms.[name] = ''' + @login + N''')'
	EXEC dbo.sp_executesql @query, N'@user sysname OUTPUT', @user OUTPUT
	IF(@user IS NULL)
		BEGIN
			SET @user = @login
			SET @statement = N'EXEC [' + @DbName + N'].dbo.sp_adduser ''' + @login + ''''
			EXEC(@statement)
			SET @ERROR = @@ERROR
			IF(@ERROR <> 0) GOTO ERROR_HANDLER
		END

	IF(@role IS NOT NULL)
		BEGIN
			SET @statement = N'EXEC [' + @DbName + N'].dbo.sp_addrolemember ''' + @role + ''', ''' + @user + ''''
			EXEC(@statement)
			SET @ERROR = @@ERROR
			IF(@ERROR <> 0) GOTO ERROR_HANDLER
		END

	SET @statement = N'	
	DECLARE OBJECT_CUR CURSOR FAST_FORWARD FOR(
		SELECT ROUTINE_NAME
		FROM [' + @DbName + '].INFORMATION_SCHEMA.ROUTINES
		WHERE ([DATA_TYPE] <> ''TABLE'')
	)
	OPEN OBJECT_CUR
	DECLARE @object sysname
	FETCH NEXT FROM OBJECT_CUR INTO @object
	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			PRINT @object
			DECLARE @statement nvarchar(512)
			SET @statement = ''GRANT EXECUTE ON ['' + @object + ''] TO [' + @user + N']''
			EXEC [' + @DbName + '].dbo.sp_executesql @statement

			FETCH NEXT FROM OBJECT_CUR INTO @object
		END
	CLOSE OBJECT_CUR
	DEALLOCATE OBJECT_CUR'
	EXEC(@statement)
	SET @ERROR = @@ERROR
	IF(@ERROR <> 0) GOTO ERROR_HANDLER	

	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0)
		SET @RC = @ERROR

EXIT_PROC:
	RETURN(@RC)
END